"""PACP Database Utility
Connecting to a sqlite PACP database and query the database
"""
import os, sqlite3, logging
from logger import create_logger
logger = create_logger('pacp')
logger.setLevel(logging.INFO)
try:
    import pyodbc
except:
    logger.info('No pyodbc moudule found.')

PASSWORD = 'sigchi'

DETAILS = {
           'Severity': ['Integer', ''], 
           'Grout_Volume': ['Float', '.2f'], 
           'MHID': ['Integer', ''], 
           'Percentage': ['Integer', ''],
           'Continuous': ['Text', ''],
            'Distance_To': ['Float', '.2f'],
             'DetailID': ['Integer', '', 'PK'], 
             'RunID': ['Integer', '', 'FK'], 
             'Position_From': ['Text', ''], 
             'Joint_Number': ['Text', ''], 
             'Connection_Address': ['Text', ''], 
             'Dimension2': ['Text', ''], 
             'Intrusion': ['Float', '.2f'], 
             'Dimension1': ['Text', ''], 
             'ConnectionID': ['Integer', ''], 
             'Position_To': ['Text', ''], 
             'ModifierCode': ['Text', ''], 
             'Pass_Fail': ['Text', ''], 
             'Comment': ['Text', ''], 
             'Footage': ['Float', '.2f'], 
             'ConditionCode': ['Text', ''], 
             'VideoFrameNumber': ['Integer', ''], 
             'Measured_From_Up': ['Text', '']}




RUNS = {'Rim_to_Grade_(D)': ['Float', '.2f'], 
        'City': ['Text', ''], 
        'Upstream_MH': ['Text', ''], 
        'PO_Number': ['Text', ''], 
        'Rim_to_Invert_(D)': ['Float', '.2f'], 
        'Surveyor_Name': ['Text', ''], 
        'Downstream_MH': ['Text', ''], 
        'Structural_Grading': ['Text', ''], 
        'Pre-Cleaning': ['Text', ''], 
        'Year_Renewed': ['Integer', ''], 
        'Location_Details': ['Text', ''], 
        'Rim_to_Invert_(U)': ['Float', '.2f'], 
        'Grade_to_Invert_(U)': ['Float', '.2f'], 
        'Year_Laid': ['Integer', ''], 
        'Grade_to_Invert_(D)': ['Float', '.2f'], 
        'Pipe_Joint_Length': ['Float', '.2f'], 
        'Length_Surveyed': ['Float', '.2f'], 
        'Material': ['Text', ''], 
        'Direction_of_Survey': ['Text', ''], 
        'Additional_Info': ['Text', ''], 
        'DesignSlope': ['Float', '.2f'], 
        'Location_Code': ['Text', ''], 
        'Sewer_Use': ['Text', ''], 
        'RunID': ['Integer', '', 'PK'], 
        'Date': ['Text', ''], 
        'MPEGPath': ['Text', ''], 
        'DiscName': ['Text', ''], 
        'Purpose': ['Text', ''], 
        'Customer': ['Text', ''], 
        'Pipe_Segment_Reference': ['Text', ''], 
        'Sewer_Category': ['Text', ''], 
        'Weather': ['Text', ''], 
        'Certificate_Number': ['Text', ''], 
        'Time': ['Text', ''], 
#        'O_&_M_Grading': ['Text', ''], 
        'Date_Cleaned': ['Text', ''], 
        'Drainage_Area': ['Text', ''], 
        'Width': ['Integer', ''], 
        'Media_Label': ['Text', ''], 
        'EndFrame': ['Integer', ''], 
        'PIPETECHID': ['Text', ''], 
        'Shape': ['Text', ''], 
        'Street': ['Text', ''], 
        'Lining_Method': ['Text', ''], 
        'Flow_Control': ['Text', ''], 
        'Height_(Diameter)': ['Integer', ''], 
        'Owner': ['Text', ''], 
        'Total_Length': ['Float', '.2f'], 
#        'GIS_Map': ['Text', ''], 
        'Rim_to_Grade_(U)': ['Float', '.2f']}

RUNS_FLDS = RUNS.keys()
DETAILS_FLDS = DETAILS.keys()
SCHEMA = {'Runs': RUNS_FLDS, 'Details': DETAILS_FLDS}

def get_connection(db, password):
    """
    A helper function to return a cursor to the MDB/sqlite file
    """
    #only for sqlite
    conn = sqlite3.connect(db)
    #c = conn.cursor()
    return conn



class Record(object):
    '''
    The Record class is used to compare the changes of a record.
    r = Record(100, {'a': 100, 'b': 200}
    r['a'] = 300
    r['b'] = 400
    print r.get_changes()
    
    '''
    def __init__(self, key=None, values={}, protected_fields=[]):
        self.values = values
        self._old = {}
        self.id = key
        self.protected_fields = protected_fields
    def __getitem__(self, name):
        return self.values.get(name)

    def __setitem__(self, name, value):
        """Log ticket modifications so the table ticket_change can be updated
        """
        if name in self.values and self.values[name] == value:
            return
        if name in self.protected_fields:
            return

        if name not in self._old: # Changed field
            #if it is a new value, return None
            self._old[name] = self.values.get(name)
        elif self._old[name] == value: # Change of field reverted
            del self._old[name]
        self.values[name] = value

    def get_changes(self):
        changes = []
        for name in self._old.keys():
            changes.append({'field': name, 'old': self._old[name], 'new': self.values[name]})
        return changes
    def __repr__(self):
        return '<record object>\nvalues:%s\nchanges:%s' % (str(self.values), str(self.get_changes()))
    



class PACPLibrary(object):
    def __init__(self, src, password=PASSWORD, schema=SCHEMA):
        self.src = src
        self.schema = schema
        self.password = password
        self.connection = get_connection(self.src, self.password)
    def getRuns(self, query, limit=20, offset=0):
        '''return a list of records'''
        c = self.connection.cursor()
        fields = RUNS_FLDS
        if query.lower()=='all':
            sql = 'select %s from Runs order by PIPETECHID limit ? offset ? ' %  ','.join(['"%s"' % x for x in fields])
            print sql
            p = [limit, offset]
        else:
            sql = "select %s from Runs where PIPETECHID like '%s' order by PIPETECHID limit ?  offset ?" % (','.join(['"%s"' % x for x in fields]), '%%%s%%' % query.replace(':', '_'))
            p = [limit, offset]    
        c.execute(sql, p)
        results = []
        for f in c:
            results.append(dict(zip(fields, f)))
        tct = 0
        for x in c.execute('select count() from runs'):
            tct = x[0]
        if len(results)==0:
            if len(query.split('_'))>1:
                sql = "select %s from Runs where %s order by PIPETECHID limit ?  offset ?" % (','.join(['"%s"' % x for x in fields]), ' or '.join(["PIPETECHID LIKE '%%%s%%'"  % x for x in  query.split('_')]))
                c.execute(sql, p)
                results = []
                for f in c:
                    results.append(dict(zip(fields, f)))
        return results
    def getDetails(self, runid):
        run = Run(self.src, runid)
        return run.getDetails()
    def runCount(self):
        c = self.connection.cursor()
        tct = 0
        for x in c.execute('select count() from runs'):
            tct = x[0]
        return tct
        





class Run(object):
    '''
    get the run and defects from a pacp pipetech database
    '''
    def __init__(self, src, runid, password=PASSWORD, schema=SCHEMA):
        self.src = src
        self.password = password
        self.schema = schema
        self.runid = runid
        self.connection = get_connection(self.src, self.password)
        self.getRun()
    
    def getRun(self):
        c = self.connection.cursor()
        flds = ','.join(['"%s"' % x for x in self.schema['Runs']])
        sql = 'select %s from Runs where RunID=?' % (flds)
        row = c.execute(sql, [self.runid]).fetchone()
        result = {}
        if row:
            result = dict(zip(self.schema['Runs'], row))
#            for fld in self.schema['Runs']:
#                    result[fld] = getattr(row, fld)
        self.run = Record(result['PIPETECHID'], result,['RunID', 'O_&_M_Grading', 'MPEGPath', 'EndFrame'])
    def getDetails(self):
        c = self.connection.cursor()
        flds = ','.join(['"%s"' % x for x in self.schema['Details']])
        sql = 'select %s from Details where RunID=? order by Footage' % (flds)
        
        results = []
        for row in c.execute(sql, [self.runid]):
            result = dict(zip(self.schema['Details'], row))
#            result = {}
#            for fld in self.schema['Details']:
#                result[fld] = getattr(row, fld)
            results.append(result)
        return results
    
    
def test():
    src = 'data/pacp.sqlite'
    p = PACPLibrary(src)
    print p.runCount()
    for r in p.getRuns('0016c'):
        runid = r['RunID']
        print r['PIPETECHID']
        for d in p.getDetails(runid):
            print '  --', d['ConditionCode'], d['Footage']
#test()

